<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Database Tables : DataMapper User Guide</title>

<style type='text/css' media='all'>@import url('../css/userguide.css');</style>
<link rel='stylesheet' type='text/css' media='all' href='../css/userguide.css' />

<meta http-equiv='expires' content='-1' />
<meta http-equiv= 'pragma' content='no-cache' />
<meta name='robots' content='all' />

</head>

<body>

<!-- START NAVIGATION -->
<div id="nav"><div id="nav_inner"></div></div>
<div id="nav2"><a name="top">&nbsp;</a><a id="nav_toggle" href="#"><img src="../images/nav_toggle_darker.jpg" width="154" height="43" border="0" title="Toggle Table of Contents" alt="Toggle Table of Contents" /></a></div>
<div id="masthead">
<table cellpadding="0" cellspacing="0" border="0" style="width:100%">
<tr>
<td><h1>DataMapper</h1></td>
<td id="breadcrumb_right"><a href="toc.html">Table of Contents Page</a></td>
</tr>
</table>
</div>
<!-- END NAVIGATION -->

<!-- START BREADCRUMB -->
<table cellpadding="0" cellspacing="0" border="0" style="width:100%">
<tr>
<td id="breadcrumb">
<a href="http://stensi.com/">DataMapper Home</a> &nbsp;&#8250;&nbsp;
<a href="../index.html">User Guide Home</a> &nbsp;&#8250;&nbsp;
Database Tables
</td>
</tr>

</table>
<!-- END BREADCRUMB -->

<br clear="all" />


<!-- START CONTENT -->
<div id="content">


<h1>Database Tables</h1>

<p>DataMapper is implemented with <a href="http://en.wikipedia.org/wiki/Database_normalization">Database normalization</a> <a href="http://en.wikipedia.org/wiki/Fifth_normal_form">Fifth normal form</a> in mind. In short, that means every table is aware only of itself, with fields relevant only to itself.  If a table has a relationship with another table, it is represented by a special joining table.  These joining tables hold all the relationships between records of other tables.</p>

<p>Lets take a look at the below example.</p>

<h3>countries</h3>
<table cellpadding="0" cellspacing="1" border="0" style="width:25%" class="tableborder">
<tr>
	<th>id</th>
	<th>code</th>
	<th>name</th>
</tr>
<tr>
	<td class="td">12</td>
	<td class="td">AM</td>
	<td class="td">Armenia</td>
</tr>
<tr>
	<td class="td">13</td>
	<td class="td">AW</td>
	<td class="td">Aruba</td>
</tr>
<tr>
	<td class="td">14</td>
	<td class="td">AU</td>
	<td class="td">Australia</td>
</tr>
<tr>
	<td class="td">15</td>
	<td class="td">AT</td>
	<td class="td">Austria</td>
</tr>
</table>

<h3>countries_users</h3>
<table cellpadding="0" cellspacing="1" border="0" style="width:20%" class="tableborder">
<tr>
	<th>id</th>
	<th>country_id</th>
	<th>user_id</th>
</tr>
<tr>
	<td class="td">1</td>
	<td class="td">14</td>
	<td class="td">7</td>
</tr>
<tr>
	<td class="td">1</td>
	<td class="td">12</td>
	<td class="td">8</td>
</tr>
</table>

<h3>users</h3>
<table cellpadding="0" cellspacing="1" border="0" style="width:25%" class="tableborder">
<tr>
	<th>id</th>
	<th>username</th>
	<th>password</th>
	<th>email</th>

</tr>
<tr>
	<td class="td">7</td>
	<td class="td">Foo</td>
	<td class="td">ec773c1da6f96b0265d76fa0a53db697e66a8eea</td>
	<td class="td">foo@bar.com</td>
</tr>
<tr>
	<td class="td">8</td>
	<td class="td">Baz</td>
	<td class="td">383f27f548397ea123ec444505ef4c7cd993dbf6</td>
	<td class="td">baz@qux.com</td>
</tr>
</table>

<p>Here we have 3 tables.  Tables <b>countries</b> and <b>users</b> are normal tables.  Table <b>countries_users</b> is the joining table that stores the relations between the records of countries and users.</p>

<p>The joining table shows that country ID 14 (Australia) has a relationship with user ID 7 (Foo).  Country ID 12 (Armenia) has a relationship with user ID 8 (Baz).</p>

<h2>Rules</h2>

<ul>
	<li>Every table must have a primary key named <b>id</b>.</li>
	<li>Normal tables must be named the lowercase, pluralised version of the object name. So for a user object of <b>User</b>, the table would be named <b>users</b>.  For <b>Country</b>, it would be <b>countries</b>.</li>
	<li>A joining table must exist between each related normal tables, regardless of the type of relationship.</li>
	<li>Joining tables must be named with both of the table names it is joining, in alphabetical order, separated by an underscore (_). For example, the joining table for <b>users</b> and <b>countries</b> is <b>countries_users</b>.</li>
	<li>Joining tables must have a specially name id field for each of the tables it is joining, named as the singular of the table name, followed by an underscore (_) and the word <b>id</b>. For example, the joining id field name for table <b>users</b> would be <b>user_id</b>.  The joining id field name for table <b>countries</b> would be <b>country_id</b>.</li>
</ul>

<h3>Special rules for Self Referencing Relationships</h3>

<p>If you have a self referencing relationship (read <a href="relationtypes.html">Relationship Types</a> for more information), the joining tables have a slightly different set of rules.</p>

<ul>
	<li>Joining tables must be named with both of the model names it is joining, in lowercase alphabetical order, separated by an underscore (_). For example, if you have an <b>employees</b> table with models such as <b>Manager</b> and <b>Supervisor</b> both using employees as their table and having a relationship between each other, the joining table is <b>managers_supervisors</b> (the normal way would have been <b>employees_employees</b> which is too restrictive for self referenching relationships, as it would only allow one, and why the model names are used for this instead).</li>
	<li>Joining tables must have a specially name id field for each of the models it is joining, named as the lowercase singular of the model name, followed by an underscore (_) and the word <b>id</b>. For example, the joining id field name for model <b>Manager</b> would be <b>manager_id</b>.  The joining id field name for model <b>Supervisor</b> would be <b>supervisor_id</b>.</li>	
</ul>

<p>Here's a example of self referencing tables:</p>

<h3>employees</h3>
<table cellpadding="0" cellspacing="1" border="0" style="width:25%" class="tableborder">
<tr>
	<th>id</th>
	<th>first_name</th>
	<th>last_name</th>
	<th>position</th>

</tr>
<tr>
	<td class="td">1</td>
	<td class="td">Fred</td>
	<td class="td">Smith</td>
	<td class="td">Manager</td>
</tr>
<tr>
	<td class="td">2</td>
	<td class="td">Jayne</td>
	<td class="td">Doe</td>
	<td class="td">Supervisor</td>
</tr>
<tr>
	<td class="td">3</td>
	<td class="td">Joe</td>
	<td class="td">Public</td>
	<td class="td">Underling</td>
</tr>
<tr>
	<td class="td">4</td>
	<td class="td">Sam</td>
	<td class="td">Rogers</td>
	<td class="td">Underling</td>
</tr>
</table>


<h3>managers_supervisors</h3>
<table cellpadding="0" cellspacing="1" border="0" style="width:20%" class="tableborder">
<tr>
	<th>id</th>
	<th>manager_id</th>
	<th>supervisor_id</th>
</tr>
<tr>
	<td class="td">1</td>
	<td class="td">1</td>
	<td class="td">2</td>
</tr>
</table>


<h3>supervisors_underlings</h3>
<table cellpadding="0" cellspacing="1" border="0" style="width:20%" class="tableborder">
<tr>
	<th>id</th>
	<th>supervisor_id</th>
	<th>underling_id</th>
</tr>
<tr>
	<td class="td">1</td>
	<td class="td">2</td>
	<td class="td">3</td>
</tr>
<tr>
	<td class="td">1</td>
	<td class="td">2</td>
	<td class="td">4</td>
</tr>
</table>

<p>From the above, we can see that <b>employees</b> is a normal table and that <b>managers_supervisors</b> and <b>supervisors_underlings</b> are our self referencing joining tables. Looking at the data, we find:</p>

<p><b>Fred Smith</b> is a Manager who manages <b>Jayne Doe</b> who is a Supervisor that supervises <b>Joe Public</b> and <b>Sam Rogers</b> (both Underlings).</p>


<p>That's pretty much it as far as your tables go.  The setting to signify if tables are joined with a One to One, One to Many, or Many to Many relationship is setup in the <a href="models.html">DataMapper models</a>.</p>


</div>
<!-- END CONTENT -->


<div id="footer">
<p>
Previous Topic:&nbsp;&nbsp;<a href="reservednames.html">Reserved Names</a>
&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
<a href="#top">Top of Page</a>&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
<a href="../index.html">User Guide Home</a>&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
Next Topic:&nbsp;&nbsp;<a href="models.html">DataMapper Models</a>
</p>
<p><a href="http://stensi.com">DataMapper</a> &nbsp;&middot;&nbsp; Copyright &#169; 2008 &nbsp;&middot;&nbsp; <a href="http://stensi.com/">Simon Stenhouse</a></p>
<p><a href="http://codeigniter.com">CodeIgniter</a> &nbsp;&middot;&nbsp; Copyright &#169; 2006-2008 &nbsp;&middot;&nbsp; <a href="http://ellislab.com/">Ellislab, Inc.</a></p>
</div>

<script type="text/javascript" src="../js/mootools.js"></script>
<script type="text/javascript" src="../js/menu.js"></script>
<script type="text/javascript">
<!--
	window.addEvent('domready', function() {

		// Create Menu
		var menu = new Menu({
			basepath: '../',
			pagepath: ''
		});

	});	
//-->
</script>
</body>
</html>